﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ERP_RORZE_DLL.Product;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_DLL.Product.DAO;
using System.Data.SqlClient;
using ERP_RORZE_SYSTEM;
using ERP_RORZE_SYSTEM;
using ERP_RORZE_SYSTEM;

namespace ERP_RORZE.Product.MO
{
    public partial class ctrlMOList : DevExpress.XtraEditors.XtraUserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;

        //
        ClsMODAO clsMODAO = new ClsMODAO();
        DataTable dt;
        ClsMOTable b = new ClsMOTable();

        public ctrlMOList()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);
        }
        void Reload(object sender, EventArgs e)
        {
            dt = clsMODAO.GetAllMO();
            gridControl1.DataSource = dt;
        }
        private void btnReload_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            b.MONo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle,"MONo").ToString();
            clsMODAO.DeleteMO(b);
            Reload(sender, e);
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            frm_MOAdd frmAdd = new frm_MOAdd();
            frmAdd.Reload += new EventHandler(Reload);
            frmAdd.Show();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            frm_MOEdit frmEdit = new frm_MOEdit();
            frmEdit.Reload += new EventHandler(Reload);
            frmEdit.BlastQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "BlastQty").ToString();
            frmEdit.date = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "date").ToString();
            frmEdit.DeadLine = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "DeadLine").ToString();
            frmEdit.Finishdate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Finishdate").ToString();
            frmEdit.Finished = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Finished").ToString();
            frmEdit.HelisertQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "HelisertQty").ToString();
            frmEdit.Imported = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Imported").ToString();
            frmEdit.MONo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MONo").ToString();
            frmEdit.MOQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MOQty").ToString();
            frmEdit.NGQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "NGQty").ToString();
            frmEdit.Note = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Note").ToString();
            frmEdit.OKQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "OKQty").ToString();
            frmEdit.PartID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID").ToString();
            frmEdit.startdate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "startdate").ToString();
            frmEdit.Started = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Started").ToString();
            frmEdit.Show();
        }

        private void ctrlMOList_Load(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            DataSet as400 = new DataSet();
            DataSet staff = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable staff_db2;
            DataTable staff_local;                
            try
            {
                //Get Data From AS400                
                masterConnection.Open();
                sql = "select * from openquery(AS400_MPFLIB, 'Select MPPR00.PRWORK,MPOR00.* from MPOR00 Left Join MPPR00 On MPOR00.ORHNBN=MPPR00.PRHNBN Where MPOR00.ORHNBN Like ''CSZ0%'' And MPPR00.PRWORK=''W241''')";
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(as400);
                staff_db2 = as400.Tables[0];
                adapter.Dispose();
                cmd.Dispose();
                masterConnection.Close(); 
                
                //Get Data From Local Database
                myConnection.Open();
                sql = "SELECT * FROM [242_MO]";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(staff, "242_MO");
                staff_local = staff.Tables["242_MO"];
                                
                foreach (DataRow as400_row in staff_db2.Rows)
                {
                    DataRow[] current_row = staff_local.Select("MONo='" + as400_row["ORODNO"].ToString() + "'");
                    if (current_row.Length > 0)
                    {
                        foreach (DataRow local_row in current_row)
                        {
                            local_row.BeginEdit();
                            local_row["PartID"] = as400_row["ORHNBN"].ToString();
                            local_row["MOQty"] = as400_row["ORHTSU"].ToString();
                            local_row["BOrderNo"] = as400_row["SubString(MPOR00.ORFILL,50,7)"].ToString();                            
                            local_row.EndEdit();
                        }
                    }
                    else
                    {
                        DataRow new_row = staff_local.NewRow();
                        new_row["MONo"] = as400_row["ORODNO"].ToString();
                        new_row["PartID"] = as400_row["ORHNBN"].ToString();
                        new_row["MOQty"] = as400_row["MOQty"].ToString();
                        new_row["BOrderNo"] = as400_row["SubString(MPOR00.ORFILL,50,7)"].ToString();                        
                        staff_local.Rows.Add(new_row);
                    }
                }  
                               

                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(staff, "242_MO");
                
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
                
                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=4";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();

                Reload(sender,e);

                MessageBox.Show("Database has been updated successfull.");
                                
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            }
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            btnAdd_Click(sender, e);
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            btnEdit_Click(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            btnDelete_Click(sender, e);
        }
        
    }
}
